/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package exercises.day03.pt02;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Student
 */
public class FlowersDao {

    private String url = "jdbc:derby://localhost:1527/webshoppe;user=root;password=root";
    private Connection conn;

    public FlowersDao() throws ClassNotFoundException, SQLException {
        Class.forName("org.apache.derby.jdbc.ClientDriver");
        //open connection
        conn = DriverManager.getConnection(url);
    }

    public ArrayList<Flowers> listRangeFlower(int from, int to) throws SQLException, ClassNotFoundException {

        ArrayList<Flowers> flowers = new ArrayList<Flowers>();

        PreparedStatement ps = conn.prepareStatement("select * from ROOT.FLOWERSDETAILS where fprice between ? and ?  ");
        ps.setInt(1, from);
        ps.setInt(2, to);

        ResultSet rs = ps.executeQuery();

        //STEP 5: Iterate through the results set


        Flowers flower = null;

        while (rs.next()) {
            flower = new Flowers(rs.getString("BID"), rs.getString("fname"), rs.getString("fdesc"), rs.getInt("fprice"));

            flowers.add(flower);

        }
        return flowers;


    }
}
